﻿using MySqlConnector;

namespace MultiAgentsServer.Scripts.Database;

public class DataBaseConnectService
{
    private readonly string connectionString;

    public DataBaseConnectService(IConfiguration configuration)
    {
        IConfigurationSection dbConfig = configuration.GetSection("DatabaseConfig");
        connectionString = $"server={dbConfig["Server"]};port={dbConfig["Port"]};user={dbConfig["User"]};password={dbConfig["Password"]};database={dbConfig["Database"]};Pooling={dbConfig["Pooling"]};";
        TestConnection();
    }

    public void TestConnection()
    {
        try
        {
            using MySqlConnection connection = new(connectionString);
            connection.Open();
        }
        catch (Exception ex)
        {
            throw new Exception("Unable to connect to the database.", ex);
        }
    }

    public int ExecuteNonQuery(string sql, Dictionary<string, object>? parameters = null)
    {
        using MySqlConnection connection = new(connectionString);
        connection.Open();
        using MySqlCommand command = new(sql, connection);
        if (parameters != null)
        {
            foreach (KeyValuePair<string, object> param in parameters)
            {
                command.Parameters.AddWithValue(param.Key, param.Value);
            }
        }
        return command.ExecuteNonQuery();
    }

    public List<Dictionary<string, object>> ExecuteQuery(string sql, Dictionary<string, object>? parameters = null)
    {
        List<Dictionary<string, object>> resultList = [];
        using (MySqlConnection connection = new(connectionString))
        {
            connection.Open();
            using MySqlCommand command = new(sql, connection);
            if (parameters != null)
            {
                foreach (KeyValuePair<string, object> param in parameters)
                {
                    command.Parameters.AddWithValue(param.Key, param.Value);
                }
            }
            using MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Dictionary<string, object> row = [];
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    row.Add(reader.GetName(i), reader[i]);
                }
                resultList.Add(row);
            }
        }
        return resultList;
    }

    public object? ExecuteScalar(string sql, Dictionary<string, object>? parameters = null)
    {
        using MySqlConnection connection = new(connectionString);
        connection.Open();

        using MySqlCommand command = new(sql, connection);
        if (parameters != null)
        {
            foreach (KeyValuePair<string, object> param in parameters)
            {
                command.Parameters.AddWithValue(param.Key, param.Value);
            }
        }
        return command?.ExecuteScalar();
    }

    public void ExecuteInTransaction(Action<MySqlConnection, MySqlTransaction> transactionAction)
    {
        using MySqlConnection connection = new(connectionString);
        connection.Open();
        using MySqlTransaction transaction = connection.BeginTransaction();
        try
        {
            transactionAction(connection, transaction);
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }
}
